﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.Reflection;
using System.Text;
using MySql.Data.MySqlClient;

namespace Data.Serialization
{
    public class Serializer<T>
    {
        private readonly Type _type;
        private readonly TableAttribute _table;
        private readonly PropertyInfo[] _properties;
        private readonly bool _hasTable;
        private readonly bool _hasColumms;

        public Serializer()
        {
            _type = typeof(T);
            _properties = _type.GetProperties();

            if (containsTableAttribute())
            {
                _table = _type.GetCustomAttribute<TableAttribute>();
                _hasTable = true;
                _hasColumms = containsColumns();
            }

        }



        private bool containsTableAttribute()
        {
            var atts = _type.GetCustomAttribute<TableAttribute>();
            return atts != null;
        }
        private bool containsColumns()
        {
            foreach (var p in _properties)
            {
                var atts = p.GetCustomAttribute<ColumnAttribute>();

                if (atts != null)
                    return true;
            }

            return false;
        }

        public string CreateSqlSelectStatement()
        {
            if (_hasTable && _hasColumms)
            {
                StringBuilder sb = new StringBuilder("SELECT ");

                foreach (var p in _properties)
                {
                    var column = p.GetCustomAttribute<ColumnAttribute>();
                    if (column != null)
                    {
                        sb.Append(String.Format("{0}, ", column.Name ?? p.Name));
                    }
                }

                // trim off last space and comma
                sb.Remove(sb.Length - 2, 2);
                sb.Append(String.Format(" FROM {0};", _table.Name));

                return sb.ToString();
            }

            return String.Empty;
        }

        public string CreateSqlSelectByPKStatement()
        {
            StringBuilder sb = new StringBuilder("SELECT ");
            //string select = "SELECT ";
            string primaryKey = "";

            Type type = typeof(T);
            var atts = type.GetCustomAttributes(typeof(TableAttribute), false);
            TableAttribute att = (TableAttribute)atts[0];

            if (att != null)
            {
                var properties = type.GetProperties();

                foreach (var p in properties)
                {
                    var colAtts = p.GetCustomAttributes(typeof(ColumnAttribute), false);

                    if (colAtts != null)
                    {
                        ColumnAttribute colAtt = (ColumnAttribute)colAtts[0];

                        if (colAtt != null)
                        {
                            sb.Append((colAtt.Name ?? p.Name) + ", ");

                            if (colAtt.IsPrimaryKey)
                                primaryKey = colAtt.Name ?? p.Name;
                        }
                    }
                }

                sb.Remove((sb.Length - 2), 2);
                sb.Append(" FROM " + att.Name);

                // add primary key clause
                sb.Append(" WHERE " + primaryKey + " = ?;");
            }

            return sb.ToString();
        }

        public string CreateOleDbSqlInsertStatement()
        {
            if (_hasTable && _hasColumms)
            {
                StringBuilder sb = new StringBuilder();
                sb.Append(String.Format("INSERT INTO {0} (", _table.Name));

                int cols = 0;

                foreach (var p in _properties)
                {
                    var column = p.GetCustomAttribute<ColumnAttribute>();
                    sb.Append(String.Format("{0}, ", column.Name ?? p.Name));

                    ++cols;
                }

                sb.Remove(sb.Length - 2, 2); // remove space and last comma
                sb.Append(") ");

                sb.Append(" VALUES(");

                // add ?'s for oledb
                for (int i = 0; i < cols; ++i)
                    sb.Append("?,");

                sb.Remove(sb.Length - 1, 1); // remove comma
                sb.Append(");");

                return sb.ToString();

            }

            return String.Empty;
        }
        public string CreateOleDbUpdateStatement()
        {
            StringBuilder sb = new StringBuilder();

            if (_hasTable && _hasColumms)
            {
                string primaryKey = "";
                sb.Append(String.Format("UPDATE {0} SET ", _table.Name));
               
                foreach (var p in _properties)
                {
                    var col = p.GetCustomAttribute<ColumnAttribute>();

                    if (col != null)
                    {
                        string name = !String.IsNullOrEmpty(col.Name) ? col.Name : p.Name;

                        if (col.IsPrimaryKey)
                            primaryKey = !String.IsNullOrEmpty(col.Name) ? col.Name : p.Name;
                        else if (!col.ModifyOnlyOnInsert)
                            sb.Append(String.Format("{0} = ?, ", name));
                    }
                }

                sb.Remove((sb.Length - 2), 2);
                sb.Append(String.Format(" WHERE {0} = ?", primaryKey));
            }

            return sb.ToString();
        }

        // Create Dtop Statement
        // Create Delete statment



        // may need to tweek the below
        public OleDbParameter[] CreateOleDbParameters(object obj)
        {
            List<OleDbParameter> parameters = new List<OleDbParameter>();

            Type type = typeof(T);
            var atts = type.GetCustomAttributes(typeof(TableAttribute), false);
            TableAttribute att = (TableAttribute)atts[0];

            if (att != null)
            {
                var properties = type.GetProperties();

                foreach (var p in properties)
                {
                    var colAtts = p.GetCustomAttributes(typeof(ColumnAttribute), false);
                    ColumnAttribute colAtt = (ColumnAttribute)colAtts[0];

                    if (colAtt != null)
                    {
                        OleDbType dbType = OleDbType.VarChar;

                        // type check
                        if (p.PropertyType == typeof(int))
                            dbType = OleDbType.Integer;
                        else if (p.PropertyType == typeof(byte))
                            dbType = OleDbType.TinyInt;
                        else if (p.PropertyType == typeof(Guid))
                            dbType = OleDbType.Guid;
                        else if (p.PropertyType == typeof(string))
                            dbType = OleDbType.VarChar;
                        else if (p.PropertyType == typeof(DateTime))
                            dbType = OleDbType.Date;
                        else if (p.PropertyType == typeof(bool))
                            dbType = OleDbType.Boolean;

                        OleDbParameter parameter = new OleDbParameter
                        {
                            OleDbType = dbType,
                            Value = p.GetValue(obj, null)
                        };

                        if (dbType == OleDbType.VarChar && parameter.Value == null)
                            parameter.Value = String.Empty;

                        parameters.Add(parameter);
                    }
                }
            }

            return parameters.ToArray();
        }
        public OleDbParameter[] CreateUpdateOleDbParameters(object obj)
        {
            List<OleDbParameter> parameters = new List<OleDbParameter>();

            Type type = typeof(T);
            var atts = type.GetCustomAttributes(typeof(TableAttribute), false);
            TableAttribute att = (TableAttribute)atts[0];

            if (att != null)
            {
                var properties = type.GetProperties();
                OleDbParameter pk = new OleDbParameter();

                foreach (var p in properties)
                {
                    var colAtts = p.GetCustomAttributes(typeof(ColumnAttribute), false);
                    ColumnAttribute colAtt = (ColumnAttribute)colAtts[0];

                    if (colAtt != null)
                    {

                        OleDbType dbType = OleDbType.VarChar;

                        // type check
                        if (p.PropertyType == typeof(int))
                            dbType = OleDbType.Integer;
                        else if (p.PropertyType == typeof(byte))
                            dbType = OleDbType.SmallInt;
                        else if (p.PropertyType == typeof(Guid))
                            dbType = OleDbType.Guid;
                        else if (p.PropertyType == typeof(string))
                            dbType = OleDbType.VarChar;
                        else if (p.PropertyType == typeof(DateTime))
                            dbType = OleDbType.Date;
                        else if (p.PropertyType == typeof(bool))
                            dbType = OleDbType.Boolean;

                        OleDbParameter parameter = new OleDbParameter
                        {
                            OleDbType = dbType,
                            Value = p.GetValue(obj, null)
                        };

                        if (dbType == OleDbType.VarChar && parameter.Value == null)
                            parameter.Value = String.Empty;

                        if (colAtt.IsPrimaryKey)
                            pk = parameter;
                        else if (!colAtt.ModifyOnlyOnInsert)
                        {
                            parameters.Add(parameter);
                        }
                    }
                }

                parameters.Add(pk);
            }

            return parameters.ToArray();
        }
        public OleDbParameter CreateOleDbParameterForPK(object obj)
        {
            Type type = typeof(T);
            var properties = type.GetProperties();

            foreach (var p in properties)
            {
                var colAtts = p.GetCustomAttributes(typeof(ColumnAttribute), false);

                if (colAtts != null)
                {
                    ColumnAttribute col = (ColumnAttribute)colAtts[0];

                    if (col.IsPrimaryKey)
                    {
                        OleDbType dbType = OleDbType.VarChar;

                        // type check
                        if (p.PropertyType == typeof(int))
                            dbType = OleDbType.Integer;
                        else if (p.PropertyType == typeof(byte))
                            dbType = OleDbType.TinyInt;
                        else if (p.PropertyType == typeof(Guid))
                            dbType = OleDbType.Guid;
                        else if (p.PropertyType == typeof(string))
                            dbType = OleDbType.VarChar;
                        else if (p.PropertyType == typeof(DateTime))
                            dbType = OleDbType.Date;
                        else if (p.PropertyType == typeof(bool))
                            dbType = OleDbType.Boolean;

                        OleDbParameter parameter = new OleDbParameter
                        {
                            OleDbType = dbType,
                            Value = p.GetValue(obj, null)
                        };

                        return parameter;
                    }
                }
            }


            return null;
        }
        public OleDbParameter CreateOleDbParameterForPK(Guid guid)
        {
            return new OleDbParameter { OleDbType = OleDbType.Guid, Value = guid };
        }


        #region MySQL

        public MySqlParameter[] CreateMySqlParameters(object obj)
        {
            List<MySqlParameter> parameters = new List<MySqlParameter>();

            Type type = typeof(T);
            var atts = type.GetCustomAttributes(typeof(TableAttribute), false);
            TableAttribute att = (TableAttribute)atts[0];

            if (att != null)
            {
                var properties = type.GetProperties();

                foreach (var p in properties)
                {
                    var colAtts = p.GetCustomAttributes(typeof(ColumnAttribute), false);
                    ColumnAttribute colAtt = (ColumnAttribute)colAtts[0];

                    if (colAtt != null)
                    {
                        MySqlDbType dbType = MySqlDbType.VarChar;
                        object value = null;

                        // type check
                        if (p.PropertyType == typeof(int))
                        {
                            dbType = MySqlDbType.Int32;
                            value = p.GetValue(obj, null);
                        }
                        else if (p.PropertyType == typeof(double))
                        {
                            dbType = MySqlDbType.Double;
                            value = p.GetValue(obj, null);
                        }
                        else if (p.PropertyType == typeof(byte))
                        {
                            dbType = MySqlDbType.Byte;
                            value = p.GetValue(obj, null);
                        }
                        else if (p.PropertyType == typeof(Guid))
                        {
                            dbType = MySqlDbType.VarChar;
                            value = p.GetValue(obj, null);
                            value = value.ToString().TrimStart('{').TrimEnd('}');
                        }
                        else if (p.PropertyType == typeof(string))
                        {
                            dbType = MySqlDbType.VarChar;
                            value = p.GetValue(obj, null);
                        }
                        else if (p.PropertyType == typeof(DateTime))
                        {
                            dbType = MySqlDbType.DateTime;
                            value = p.GetValue(obj, null);
                        }
                        else if (p.PropertyType == typeof(bool))
                        {
                            dbType = MySqlDbType.Bit;
                            value = p.GetValue(obj, null);
                        }

                        MySqlParameter parameter = new MySqlParameter
                        {
                            MySqlDbType = dbType,
                            Value = value
                        };

                        if (dbType == MySqlDbType.VarChar && parameter.Value == null)
                            parameter.Value = String.Empty;

                        parameters.Add(parameter);
                    }
                }
            }

            return parameters.ToArray();
        }
        public MySqlParameter[] CreateUpdateMySqlParameters(object obj)
        {
            List<MySqlParameter> parameters = new List<MySqlParameter>();

            Type type = typeof(T);
            var atts = type.GetCustomAttributes(typeof(TableAttribute), false);
            TableAttribute att = (TableAttribute)atts[0];

            if (att != null)
            {
                var properties = type.GetProperties();
                MySqlParameter pk = new MySqlParameter();

                foreach (var p in properties)
                {
                    var colAtts = p.GetCustomAttributes(typeof(ColumnAttribute), false);
                    ColumnAttribute colAtt = (ColumnAttribute)colAtts[0];

                    if (colAtt != null)
                    {

                        MySqlDbType dbType = MySqlDbType.VarChar;
                        object value = null;

                        // type check
                        if (p.PropertyType == typeof(int))
                        {
                            dbType = MySqlDbType.Int32;
                            value = p.GetValue(obj, null);
                        }
                        else if (p.PropertyType == typeof(double))
                        {
                            dbType = MySqlDbType.Double;
                            value = p.GetValue(obj, null);
                        }
                        else if (p.PropertyType == typeof(byte))
                        {
                            dbType = MySqlDbType.Byte;
                            value = p.GetValue(obj, null);
                        }
                        else if (p.PropertyType == typeof(Guid))
                        {
                            dbType = MySqlDbType.VarChar;
                            value = p.GetValue(obj, null);
                            value = value.ToString().TrimStart('{').TrimEnd('}');
                        }
                        else if (p.PropertyType == typeof(string))
                        {
                            dbType = MySqlDbType.VarChar;
                            value = p.GetValue(obj, null);
                        }
                        else if (p.PropertyType == typeof(DateTime))
                        {
                            dbType = MySqlDbType.DateTime;
                            value = p.GetValue(obj, null);
                        }
                        else if (p.PropertyType == typeof(bool))
                        {
                            dbType = MySqlDbType.Bit;
                            value = p.GetValue(obj, null);
                        }

                        MySqlParameter parameter = new MySqlParameter
                        {
                            MySqlDbType = dbType,
                            Value = value
                        };

                        if (dbType == MySqlDbType.VarChar && parameter.Value == null)
                            parameter.Value = String.Empty;

                        if (colAtt.IsPrimaryKey)
                            pk = parameter;
                        else if (!colAtt.ModifyOnlyOnInsert)
                        {
                            parameters.Add(parameter);
                        }
                    }
                }

                parameters.Add(pk);
            }

            return parameters.ToArray();
        }
        public MySqlParameter CreateMySqlParameterForPK(object obj)
        {
            Type type = typeof(T);
            var properties = type.GetProperties();

            foreach (var p in properties)
            {
                var colAtts = p.GetCustomAttributes(typeof(ColumnAttribute), false);

                if (colAtts != null)
                {
                    ColumnAttribute col = (ColumnAttribute)colAtts[0];

                    if (col.IsPrimaryKey)
                    {
                        MySqlDbType dbType = MySqlDbType.VarChar;
                        object value = null;

                        // type check
                        if (p.PropertyType == typeof(int))
                        {
                            dbType = MySqlDbType.Int32;
                            value = p.GetValue(obj, null);
                        }
                        else if (p.PropertyType == typeof(double))
                        {
                            dbType = MySqlDbType.Double;
                            value = p.GetValue(obj, null);
                        }
                        else if (p.PropertyType == typeof(byte))
                        {
                            dbType = MySqlDbType.Byte;
                            value = p.GetValue(obj, null);
                        }
                        else if (p.PropertyType == typeof(Guid))
                        {
                            dbType = MySqlDbType.VarChar;
                            value = p.GetValue(obj, null);
                            value = value.ToString().TrimStart('{').TrimEnd('}');
                        }
                        else if (p.PropertyType == typeof(string))
                        {
                            dbType = MySqlDbType.VarChar;
                            value = p.GetValue(obj, null);
                        }
                        else if (p.PropertyType == typeof(DateTime))
                        {
                            dbType = MySqlDbType.DateTime;
                            value = p.GetValue(obj, null);
                        }
                        else if (p.PropertyType == typeof(bool))
                        {
                            dbType = MySqlDbType.Bit;
                            value = p.GetValue(obj, null);
                        }

                        MySqlParameter parameter = new MySqlParameter
                        {
                            MySqlDbType = dbType,
                            Value = value
                        };

                        return parameter;
                    }
                }
            }


            return null;
        }
        public MySqlParameter CreateMySqlParameterForPK(Guid guid)
        {
            return new MySqlParameter 
            { 
                MySqlDbType = MySqlDbType.VarChar,
                Value = guid.ToString().TrimStart('{').TrimEnd('}')
            };
        }

        #endregion


        public List<T> MapDataRowCollectionToClassList(DataRowCollection rows)
        {
            List<T> list = new List<T>();

            foreach (DataRow r in rows)
            {
                var obj = MapDataRowToClass(r);
                list.Add((T)obj);
            }

            return list;
        }
        public T MapDataRowToClass(DataRow row)
        {
            if (row == null)
                return default(T);

            Type type = typeof(T);
            var obj = Activator.CreateInstance(type);

            // get all properties
            var properties = type.GetProperties();

            foreach (var p in properties)
            {
                // look for column attribute
                var colAtts = p.GetCustomAttributes(typeof(ColumnAttribute), false);

                if (colAtts.Length > 0)
                {
                    ColumnAttribute colAtt = (ColumnAttribute)colAtts[0];

                    if (colAtt != null)
                    {
                        string name = !String.IsNullOrEmpty(colAtt.Name) ? colAtt.Name : p.Name;

                        object value = row[name];


                        if (p.PropertyType == typeof(Guid))
                        {
                            Guid result;

                            if (Guid.TryParse(value.ToString(), out result))
                                p.SetValue(obj, result, null);
                        }
                        else if (p.PropertyType == value.GetType())
                        {
                            p.SetValue(obj, value, null);
                        }
                    }
                }
            }


            return (T)obj;
        }
    }
}
